﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Globalization;
using System.Data.SqlServerCe;
using System.Collections;
using System.Data;

using Modelo_ETdA.Negocio.Analise;
using Modelo_ETdA.Negocio.Analise.Formato;
using Modelo_ETdA.Negocio.Analise.Avaliacao;

namespace Modelo_ETdA.Dados
{
    public class DadosDocumentos
    {
        public DadosDocumentos()
        {

        }

        public int registaFormatoAvaliacao(FormatoAvaliacao formato, int dimensao, int codAmbC)
        {
            int ret = 0;
            SqlCeCommand comando;
            SqlCeDataReader dataReader = null;
            SqlCeTransaction trans = null;
            try
            {
                String cmdstr;
                int codForm;
                trans = AdminBD.getConCe().BeginTransaction();
                switch (dimensao)
                {
                    case 1:
                        cmdstr = "SELECT cod FROM FormatoAvaliacaoCliente WHERE nome = '" + formato.Nome + "'";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        if (dataReader.Read())
                        {
                            dataReader.Close();
                            return 2; // NOME JA REGISTADO
                        }
                        else dataReader.Close();

                        FormatoAvaliacaoCliente f_cli = (FormatoAvaliacaoCliente)formato;
                        cmdstr = "INSERT INTO FormatoAvaliacaoCliente (nome,data_registo,valorCliente,apagado,codAmbC) VALUES ('" + f_cli.Nome + "', GETDATE() ," + f_cli.ValorCliente + ", 0 ," + f_cli.CodAmbC + ")";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        comando.ExecuteNonQuery();

                        cmdstr = "select cod from FormatoAvaliacaoCliente where nome ='" + f_cli.Nome + "'";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        dataReader.Read();
                        codForm = (int)dataReader["cod"];
                        dataReader.Close();

                        List<QuestaoCliente> l_q = f_cli.ListQuestaoCliente;
                        foreach (QuestaoCliente quest in l_q)
                        {
                            string ponder = quest.Ponderacao + "";
                            string ponder2 = "";
                            for (int i = 0; i < ponder.Length; i++)
                            {
                                if (ponder[i] == ',') ponder2 += '.';
                                else ponder2 += ponder[i];
                            }
                            cmdstr = "INSERT INTO QuestaoCliente (escalaMin,escalaMax,ponderacao,alcunha,codConsideracao,codFormAvalClt,minResp) VALUES ";
                            cmdstr += "(" + quest.EscalaMin + "," + quest.EscalaMax + "," + ponder2 + ",'" + quest.Alcunha + "'," + quest.CodConsideracao + "," + codForm + "," + quest.MinResposta + ")";
                            comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                            comando.ExecuteNonQuery();
                        }
                        break;

                    case 2:
                        cmdstr = "SELECT cod FROM FormatoAvaliacaoProfissional WHERE nome = '" + formato.Nome + "'";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        if (dataReader.Read())
                        {
                            dataReader.Close();
                            return 2; // NOME JA REGISTADO
                        }
                        else dataReader.Close();


                        FormatoAvaliacaoProfissional f_pro = (FormatoAvaliacaoProfissional)formato;
                        cmdstr = "INSERT INTO FormatoAvaliacaoProfissional (nome,data_registo,apagado,codAmbC) VALUES ('" + f_pro.Nome + "', GETDATE() , 0 ," + f_pro.CodAmbC + ")";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        comando.ExecuteNonQuery();

                        cmdstr = "select cod from FormatoAvaliacaoProfissional where nome ='" + f_pro.Nome + "'";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        dataReader.Read();
                        codForm = (int)dataReader["cod"];
                        dataReader.Close();

                        foreach (KeyValuePair<int, Questao> par in f_pro.MapConsideracaoQuestao)
                        {
                            cmdstr = "INSERT INTO QuestaoProfissional (escalaMin,escalaMax,ponderacao,codConsideracao,codFormAvalPro,minResp) VALUES ";
                            cmdstr += "(" + par.Value.EscalaMin + "," + par.Value.EscalaMax + "," + par.Value.Ponderacao + "," + par.Key + "," + codForm + "," + par.Value.MinResposta + ")";
                            comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                            comando.ExecuteNonQuery();
                        }
                        break;

                    case 3:
                        cmdstr = "SELECT cod FROM FormatoAvaliacaoAnalista WHERE nome = '" + formato.Nome + "'";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        if (dataReader.Read())
                        {
                            dataReader.Close();
                            return 2; // NOME JA REGISTADO
                        }
                        else dataReader.Close();

                        FormatoAvaliacaoAnalista f_anal = (FormatoAvaliacaoAnalista)formato;
                        cmdstr = "INSERT INTO FormatoAvaliacaoAnalista (nome,data_registo,minTemp,maxTemp,minRuido,maxRuido,minLum,maxLum,apagado,codAmbC) VALUES ('" + f_anal.Nome + "', GETDATE() ," + f_anal.MinTemp + "," + f_anal.MaxTemp + "," + f_anal.MinRuido + "," + f_anal.MaxRuido + "," + f_anal.MinLum + "," + f_anal.MaxLum + ", 0 ," + f_anal.CodAmbC + ")";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        comando.ExecuteNonQuery();

                        cmdstr = "select cod from FormatoAvaliacaoAnalista  where nome ='" + f_anal.Nome + "'";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        dataReader.Read();
                        codForm = (int)dataReader["cod"];
                        dataReader.Close();

                        foreach (KeyValuePair<int, List<QuestaoAnalista>> par_grande in f_anal.MapDivisaoQuestao)
                        {
                            foreach (QuestaoAnalista par in par_grande.Value)
                            {
                                cmdstr = "select cod from Divisao where codRelativo=" + par_grande.Key + "AND codAmbC =" + codAmbC + "";
                                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                                dataReader = comando.ExecuteReader();
                                int codDiv = 0;
                                if (dataReader.Read())
                                {
                                    codDiv = (int)dataReader["cod"];
                                }
                                dataReader.Close();

                                cmdstr = "INSERT INTO QuestaoAnalista (escalaMin, escalaMax, ponderacao, codDivisao, codConsideracao, codFormAvalAnal, minResp) VALUES ";
                                cmdstr += "(" + par.EscalaMin + "," + par.EscalaMax + "," + par.Ponderacao + "," + codDiv + "," + par.CodConsideracao + "," + codForm + "," + par.MinResposta + ")";
                                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                                comando.ExecuteNonQuery();
                            }
                        }
                        break;

                    default:
                        ret = 4;
                        break;
                }
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                ret = 1;
            }


            return ret;
        }

        public FormatoAvaliacao consultaFormatoAvaliacao(int codFormato, int dimensao, int codAmbC)
        {
            FormatoAvaliacao ret = null;
            SqlCeDataReader dataReader = null;
            SqlCeCommand comando;
            try
            {
                String cmdstr;
                DocumentoCabecalho d_cab = null;
                String nome = "";

                switch (dimensao)
                {
                    case 1: //Formato Avaliacao Cliente
                        FormatoAvaliacaoCliente f_cli = null;
                        List<QuestaoCliente> questoesCli = new List<QuestaoCliente>();
                        cmdstr = "SELECT nome,data_registo,valorCliente FROM FormatoAvaliacaoCliente WHERE cod = " + codFormato;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();

                        if (dataReader.Read())
                        {
                            nome = dataReader["nome"].ToString();
                            d_cab = new DocumentoCabecalho(codFormato, nome, "Cliente", (DateTime)dataReader["data_registo"], 1);
                            f_cli = new FormatoAvaliacaoCliente(codAmbC, codFormato, nome, (float)dataReader["valorCliente"], d_cab);
                        }
                        dataReader.Close();

                        cmdstr = "SELECT cod, escalaMin, escalaMax, ponderacao, alcunha, codConsideracao,minResp FROM QuestaoCliente WHERE codFormAvalClt=" + codFormato;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        string alcunha;
                        QuestaoCliente questClt;
                        int codBdClt, escalaMinClt, escalaMaxClt, codConsideracaoClt, minRespClt;
                        float ponderacaoClt;
                        while (dataReader.Read())
                        {
                            codBdClt = (int)dataReader["cod"];
                            escalaMinClt = (int)dataReader["escalaMin"];
                            escalaMaxClt = (int)dataReader["escalaMax"];
                            ponderacaoClt = (float)dataReader["ponderacao"];
                            alcunha = (string)dataReader["alcunha"];
                            minRespClt = (int)dataReader["minResp"];
                            codConsideracaoClt = (int)dataReader["codConsideracao"];
                            questClt = new QuestaoCliente(codBdClt, alcunha, escalaMinClt, escalaMaxClt, minRespClt, ponderacaoClt, codConsideracaoClt);
                            questoesCli.Add(questClt);
                        }
                        f_cli.ListQuestaoCliente = questoesCli;
                        dataReader.Close();

                        ret = f_cli;
                        break;

                    case 2: //Formato Avaliação Profissional
                        FormatoAvaliacaoProfissional f_pro = null;
                        Dictionary<int, Questao> questoesPro = new Dictionary<int, Questao>();
                        cmdstr = "SELECT nome,data_registo FROM FormatoAvaliacaoProfissional WHERE cod = " + codFormato;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        if (dataReader.Read())
                        {
                            nome = dataReader["nome"].ToString();
                            d_cab = new DocumentoCabecalho(codFormato, nome, "Profissional", (DateTime)dataReader["data_registo"], 2);
                        }
                        dataReader.Close();

                        cmdstr = "SELECT cod, escalaMin, escalaMax, ponderacao, codConsideracao, minResp FROM QuestaoProfissional WHERE codFormAvalPro=" + codFormato;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        Questao questPro;
                        int codBdPro, escalaMinPro, escalaMaxPro, codConsideracaoPro, minRespPro;
                        float ponderacaoPro;
                        while (dataReader.Read())
                        {
                            codBdPro = (int)dataReader["cod"];
                            escalaMinPro = (int)dataReader["escalaMin"];
                            escalaMaxPro = (int)dataReader["escalaMax"];
                            ponderacaoPro = (float)dataReader["ponderacao"];
                            minRespPro = (int)dataReader["minResp"];
                            codConsideracaoPro = (int)dataReader["codConsideracao"];
                            questPro = new Questao(codBdPro, escalaMinPro, escalaMaxPro, minRespPro, ponderacaoPro, codConsideracaoPro);
                            questoesPro.Add(codConsideracaoPro, questPro);
                        }
                        dataReader.Close();

                        f_pro = new FormatoAvaliacaoProfissional(codAmbC, codFormato, nome, d_cab, questoesPro);
                        ret = f_pro;
                        break;

                    case 3: //Formato Avaliação Analista
                        FormatoAvaliacaoAnalista f_anal = null;
                        cmdstr = "SELECT nome,data_registo,minTemp,maxTemp,minRuido,maxRuido,minLum,maxLum FROM FormatoAvaliacaoAnalista WHERE cod = " + codFormato;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();

                        if (dataReader.Read())
                        {
                            nome = dataReader["nome"].ToString();
                            d_cab = new DocumentoCabecalho(codFormato, nome, "Analista", (DateTime)dataReader["data_registo"], 2);
                            f_anal = new FormatoAvaliacaoAnalista(codAmbC, codFormato, nome, (int)dataReader["minRuido"], (int)dataReader["minTemp"], (int)dataReader["minLum"], (int)dataReader["maxRuido"], (int)dataReader["maxTemp"], (int)dataReader["maxLum"], d_cab);
                        }
                        dataReader.Close();

                        cmdstr = "SELECT cod, escalaMin, escalaMax, ponderacao, codConsideracao, minResp, codDivisao FROM QuestaoAnalista WHERE codFormAvalAnal=" + codFormato;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        int codBdAnal, escalaMinAnal, escalaMaxAnal, codConsideracaoAnal, minRespAnal, codDivAna;
                        float ponderacaoAnal;
                        while (dataReader.Read())
                        {
                            codBdAnal = (int)dataReader["cod"];
                            escalaMinAnal = (int)dataReader["escalaMin"];
                            escalaMaxAnal = (int)dataReader["escalaMax"];
                            ponderacaoAnal = (float)dataReader["ponderacao"];
                            codDivAna = (int)dataReader["codDivisao"];
                            minRespAnal = (int)dataReader["minResp"];
                            codConsideracaoAnal = (int)dataReader["codConsideracao"];
                            f_anal.registMin(codBdAnal, codDivAna, minRespAnal, codConsideracaoAnal);
                        }
                        dataReader.Close();
                        ret = f_anal;
                        break;
                    default:
                        ret = null;
                        break;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                ret = null;
            }

            return ret;
        }

        public int getCodQuestao(int codCons, int codDiv, int codForm)
        {
            int codQuestao = -1;
            SqlCeDataReader dataReader = null;
            try
            {
                string cmdstr = "select cod from QuestaoAnalista where codConsideracao =" + codCons + " AND codDivisao=" + codDiv + " AND codFormAvalAnal =" + codForm;
                SqlCeCommand comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    codQuestao = (int)dataReader["cod"];
                }
                dataReader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
            }
            return codQuestao;
        }

        public int removeDocumento(int codDoc, int dim)
        {
            int ret = 0;
            SqlCeCommand comando;
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                String cmdstr;
                switch (dim)
                {
                    case 1:
                        cmdstr = "UPDATE FormatoAvaliacaoCliente SET apagado = 1 WHERE cod = " + codDoc;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        comando.ExecuteNonQuery();

                        break;
                    case 2:
                        cmdstr = "UPDATE FormatoAvaliacaoProfissional SET apagado = 1 WHERE cod = " + codDoc;

                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        comando.ExecuteNonQuery();

                        break;
                    case 3:
                        cmdstr = "UPDATE FormatoAvaliacaoAnalista SET apagado = 1 WHERE cod = " + codDoc;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        comando.ExecuteNonQuery();

                        break;
                    default:
                        ret = 4;
                        break;
                }
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                ret = 1;
            }
            return ret;
        }

        public List<Habilitacao> getListHabilitacaoLiteraria()
        {
            List<Habilitacao> l_hab = new List<Habilitacao>();
            Habilitacao aux;
            int cod;
            String desc;
            SqlCeDataReader dataReader = null;

            try
            {
                string cmdstr = "select * from Habilitacao ORDER BY cod";
                SqlCeCommand comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                while (dataReader.Read())
                {
                    cod = (int)dataReader["cod"];
                    desc = dataReader["descricao"].ToString();
                    aux = new Habilitacao(cod, desc);
                    l_hab.Add(aux);
                }
                dataReader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
            }
            return l_hab;
        }

        public int addHabilitacao(String descricao)
        {
            SqlCeCommand comando;
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                string cmdstr = "insert into Habilitacao (descricao) values ('" + descricao + "')";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                return 1;
            }
            return 0;
        }

        public int registListQuestionario(List<Questionario> l_quest)
        {
            SqlCeCommand comando;
            SqlCeDataReader dataReader = null;
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                Questionario master = l_quest[l_quest.Count - 1];
                string cmdstr = "SELECT cod FROM AvaliacaoCliente WHERE nome = '" + master.Doc_cabecalho.Nome + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    dataReader.Close();
                    return 2; // NOME JA REGISTADO
                }
                else dataReader.Close();
                cmdstr = "INSERT INTO AvaliacaoCliente (codAmbC,codFormAvalCliente,nome,data_registo) VALUES (" + master.CodAmbC + "," + master.CodForm + ",'" + master.Doc_cabecalho.Nome + "', GETDATE() )";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();

                cmdstr = "SELECT cod FROM AvaliacaoCliente WHERE nome = '" + master.Doc_cabecalho.Nome + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                int cod_aval = 0;
                if (dataReader.Read())
                    cod_aval = Convert.ToInt32(dataReader["cod"]);
                dataReader.Close();

                Boolean prim = true;
                int b_gen;
                int b_clt;


                foreach (Questionario questionario in l_quest)
                {
                    if (questionario.Genero) b_gen = 1;
                    else b_gen = 0;
                    if (questionario.Cliente) b_clt = 1;
                    else b_clt = 0;
                    cmdstr = "INSERT INTO Questionario (idade,Genero,Cliente,codAvalCliente,codHabilitacao) VALUES ";
                    cmdstr += "(" + questionario.Idade + "," + b_gen + "," + b_clt + "," + cod_aval + "," + questionario.CodHabilitacao + ")";
                    comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                    comando.ExecuteNonQuery();
                }

                int n_cod = 0;
                cmdstr = "SELECT MAX(cod) as maximo FROM Questionario";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                    n_cod = Convert.ToInt32(dataReader["maximo"]);
                dataReader.Close();

                int i = l_quest.Count - 1;
                Questionario aux;
                prim = true;

                while (i >= 0)
                {
                    prim = true;
                    aux = l_quest[i];
                    foreach (Resposta resp in aux.Respostas)
                    {
                        if (prim) prim = false;
                        else cmdstr += ",";
                        cmdstr = "INSERT INTO RespostaCliente (codQuestaoCliente,codQuestionario,valorResposta) VALUES ";
                        cmdstr += "(" + resp.CodQuestao + "," + n_cod + "," + resp.ValorResposta + ")";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        comando.ExecuteNonQuery();
                    }
                    n_cod--;
                    i--;
                }
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                return 1;
            }
            return 0;
        }

        public int registListFormAval(List<FormularioAvaliacao> l_fa)
        {
            SqlCeCommand comando;
            SqlCeDataReader dataReader = null;
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                FormularioAvaliacao master = l_fa[l_fa.Count - 1];
                string cmdstr = "SELECT cod FROM AvaliacaoProfissional WHERE nome = '" + master.Doc_cabecalho.Nome + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    dataReader.Close();
                    return 2; // NOME JA REGISTADO
                }
                else dataReader.Close();

                cmdstr = "INSERT INTO AvaliacaoProfissional (codAmbC,codFormAvalProfissional,nome,data_registo) VALUES (" + master.CodAmbC + "," + master.CodForm + ",'" + master.Doc_cabecalho.Nome + "', GETDATE() )";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();

                cmdstr = "SELECT cod FROM AvaliacaoProfissional WHERE nome = '" + master.Doc_cabecalho.Nome + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                int cod_aval = 0;
                if (dataReader.Read())
                {
                    cod_aval = (int)dataReader["cod"];
                }
                dataReader.Close();

                foreach (FormularioAvaliacao faval in l_fa)
                {
                    cmdstr = "INSERT INTO FormularioAvaliacao (codAvalProfissional,codDivisao) VALUES ";
                    cmdstr += "(" + cod_aval + "," + faval.CodDivisao + ")";
                    comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                    comando.ExecuteNonQuery();
                }

                int n_cod = 0;
                cmdstr = "SELECT MAX(cod) as topMax FROM FormularioAvaliacao";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    n_cod = (int)dataReader["topMax"];
                }
                dataReader.Close();

                int i = l_fa.Count - 1;
                FormularioAvaliacao aux;

                while (i >= 0)
                {
                    aux = l_fa[i];
                    foreach (Resposta resp in aux.Respostas)
                    {
                        cmdstr = "INSERT INTO RespostaProfissional (codQuestaoProfissional,codFormularioAvaliacao,valorResposta) VALUES ";
                        cmdstr += "(" + resp.CodQuestao + "," + n_cod + "," + resp.ValorResposta + ")";
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        comando.ExecuteNonQuery();
                    }
                    n_cod--;
                    i--;
                }
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                return 1;
            }
            return 0;
        }

        public int registChecklist(Checklist clist)
        {
            SqlCeCommand comando;
            SqlCeDataReader dataReader = null;
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                string cmdstr = "SELECT cod FROM AvaliacaoAnalista WHERE nome = '" + clist.Doc_cabecalho.Nome + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    dataReader.Close();
                    return 2; // NOME JA REGISTADO
                }
                else dataReader.Close();

                cmdstr = "INSERT INTO AvaliacaoAnalista (codAmbC,codFormAvalAnalista,nome,data_registo) VALUES (" + clist.CodAmbC + "," + clist.CodForm + ",'" + clist.Doc_cabecalho.Nome + "', GETDATE() )";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();

                cmdstr = "SELECT cod FROM AvaliacaoAnalista WHERE nome = '" + clist.Doc_cabecalho.Nome + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                int cod_aval = -2;
                if (dataReader.Read())
                {
                    cod_aval = (int)dataReader["cod"];
                }
                dataReader.Close();

                cmdstr = "INSERT INTO Checklist (valTemp,valRuido,valLum,codAvalAnalista) VALUES (" + clist.ValTemp + "," + clist.ValRuido + "," + clist.ValLum + "," + cod_aval + ")";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();

                cmdstr = "SELECT MAX(cod) as topMax FROM CHECKLIST";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                int cod_chk = -2;
                if (dataReader.Read())
                {
                    cod_chk = (int)dataReader["topMax"];
                }
                dataReader.Close();

                foreach (Resposta resp in clist.Respostas)
                {
                    cmdstr = "INSERT INTO RespostaAnalista (codQuestaoAnalista,codChecklist,valorResposta) VALUES ";
                    cmdstr += "(" + resp.CodQuestao + "," + cod_chk + "," + resp.ValorResposta + ")";
                    comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                    comando.ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                return 1;
            }
            return 0;
        }

        public List<Avaliacao> consultaAvaliacao(int codAval, int dimensao)
        {
            List<Avaliacao> ret = null;
            SqlCeDataReader dataReader = null;
            try
            {
                SqlCeCommand comando;
                String cmdstr;
                int codAmbC;
                int codForm;
                String nome;
                DateTime data_registo;

                switch (dimensao)
                {
                    case 1:
                        List<Avaliacao> l_quest = new List<Avaliacao>();
                        cmdstr = "SELECT codAmbC,codFormAvalCliente,nome,data_registo FROM AvaliacaoCliente WHERE cod = " + codAval;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        if (dataReader.Read())
                        {
                            codAmbC = (int)dataReader["codAmbC"];
                            codForm = (int)dataReader["codFormAvalCliente"];
                            nome = dataReader["nome"].ToString();
                            data_registo = (DateTime)dataReader["data_registo"];
                        }
                        else
                        {
                            dataReader.Close();
                            return null;
                        }
                        dataReader.Close();

                        cmdstr = "SELECT cod,idade,Genero,Cliente,codHabilitacao FROM Questionario WHERE codAvalCliente = " + codAval;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        Questionario quest_aux;
                        int cod_quest;
                        int idade;
                        Boolean genero;
                        Boolean cliente;
                        int codHabilitacao;
                        while (dataReader.Read())
                        {
                            cod_quest = (int)dataReader["cod"];
                            idade = (int)dataReader["idade"];
                            genero = Convert.ToInt32(dataReader["Genero"]) == 1;
                            cliente = Convert.ToInt32(dataReader["Cliente"]) == 1;
                            codHabilitacao = (int)dataReader["codHabilitacao"];

                            quest_aux = new Questionario(codAval, cod_quest, codForm, new DocumentoCabecalho(codAval, nome, "Cliente", data_registo, 2), codAmbC, idade, genero, cliente, codHabilitacao);
                            l_quest.Add(quest_aux);
                        }
                        dataReader.Close();
                        List<Resposta> l_quest_resp = null;
                        Resposta quest_resp;

                        foreach (Questionario quest in l_quest)
                        {
                            cmdstr = "SELECT cod,codQuestaoCliente,valorResposta FROM RespostaCliente WHERE codQuestionario = " + quest.CodQuest;
                            comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                            dataReader = comando.ExecuteReader();

                            l_quest_resp = new List<Resposta>();
                            while (dataReader.Read())
                            {
                                quest_resp = new Resposta((int)dataReader["cod"], (int)dataReader["codQuestaoCliente"], (int)dataReader["valorResposta"]);
                                l_quest_resp.Add(quest_resp);
                            }
                            dataReader.Close();
                            quest.Respostas = l_quest_resp;
                            l_quest_resp = null;
                        }

                        ret = l_quest;
                        break;

                    case 2:
                        List<Avaliacao> l_fa = new List<Avaliacao>();
                        cmdstr = "SELECT codAmbC,codFormAvalProfissional,nome,data_registo FROM AvaliacaoProfissional WHERE cod = " + codAval;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        if (dataReader.Read())
                        {
                            codAmbC = (int)dataReader["codAmbC"];
                            codForm = (int)dataReader["codFormAvalProfissional"];
                            nome = dataReader["nome"].ToString();
                            data_registo = (DateTime)dataReader["data_registo"];
                        }
                        else
                        {
                            dataReader.Close();
                            return null;
                        }
                        dataReader.Close();

                        cmdstr = "SELECT cod,codDivisao FROM FormularioAvaliacao where codAvalProfissional = " + codAval;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();
                        FormularioAvaliacao form_aux;
                        int cod_fa;
                        int cod_Div;
                        while (dataReader.Read())
                        {
                            cod_fa = (int)dataReader["cod"];
                            cod_Div = (int)dataReader["codDivisao"];

                            form_aux = new FormularioAvaliacao(codForm, new DocumentoCabecalho(codForm, nome, "Profissional", data_registo, 2), codAval, cod_fa, codAmbC, cod_Div);
                            l_fa.Add(form_aux);
                        }
                        dataReader.Close();
                        List<Resposta> l_fa_resp = null;
                        Resposta fa_resp;

                        foreach (FormularioAvaliacao fa in l_fa)
                        {
                            cmdstr = "SELECT cod,codQuestaoProfissional,valorResposta FROM RespostaProfissional WHERE codFormularioAvaliacao = " + fa.Cod_FA;
                            comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                            dataReader = comando.ExecuteReader();

                            l_fa_resp = new List<Resposta>();
                            while (dataReader.Read())
                            {

                                fa_resp = new Resposta((int)dataReader["cod"], (int)dataReader["codQuestaoProfissional"], (int)dataReader["valorResposta"]);
                                l_fa_resp.Add(fa_resp);
                            }
                            dataReader.Close();
                            fa.Respostas = l_fa_resp;
                            l_fa_resp = null;
                        }

                        ret = l_fa;
                        break;

                    case 3:

                        List<Avaliacao> l_chk = new List<Avaliacao>();
                        cmdstr = "SELECT codAmbC,codFormAvalAnalista,nome,data_registo FROM AvaliacaoAnalista WHERE cod = " + codAval;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();

                        if (dataReader.Read())
                        {
                            codAmbC = (int)dataReader["codAmbC"];
                            codForm = (int)dataReader["codFormAvalAnalista"];
                            nome = dataReader["nome"].ToString();
                            data_registo = (DateTime)dataReader["data_registo"];
                        }
                        else
                        {
                            dataReader.Close();
                            return null;
                        }

                        dataReader.Close();

                        cmdstr = "SELECT cod,valTemp,valRuido,valLum FROM Checklist WHERE codAvalAnalista = " + codAval;
                        comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                        dataReader = comando.ExecuteReader();

                        Checklist chk_aux;
                        int cod_chk;
                        int valTemp, valRuido, valLum;
                        if (dataReader.Read())
                        {
                            cod_chk = (int)dataReader["cod"];
                            valTemp = (int)dataReader["valTemp"];
                            valRuido = (int)dataReader["valRuido"];
                            valLum = (int)dataReader["valLum"];
                            chk_aux = new Checklist(codForm, new DocumentoCabecalho(codForm, nome, "Analista", data_registo, 2), codAval, cod_chk, codAmbC, valRuido, valTemp, valLum);
                            l_chk.Add(chk_aux);
                        }
                        else
                        {
                            dataReader.Close();
                            return null;
                        }
                        dataReader.Close();
                        List<Resposta> l_chk_resp = null;
                        Resposta chk_resp;

                        foreach (Checklist chk in l_chk)
                        {
                            cmdstr = "SELECT cod,codQuestaoAnalista,valorResposta FROM RespostaAnalista WHERE codChecklist = " + chk.Cod_CHK;
                            comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                            dataReader = comando.ExecuteReader();

                            l_chk_resp = new List<Resposta>();
                            while (dataReader.Read())
                            {

                                chk_resp = new Resposta((int)dataReader["cod"], (int)dataReader["codQuestaoAnalista"], (int)dataReader["valorResposta"]);
                                l_chk_resp.Add(chk_resp);
                            }
                            dataReader.Close();
                            chk.Respostas = l_chk_resp;
                            l_chk_resp = null;
                        }

                        ret = l_chk;
                        break;

                    default:
                        ret = null;
                        break;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                return null;
            }
            return ret;
        }

        public DataTable getListAvalForm(int codAmb)
        {
            DataTable dt = new DataTable();
            try
            {
                //formatos
                using (SqlCeDataAdapter a = new SqlCeDataAdapter("select cod, nome , 'Formato', 'Analista' , data_registo from FormatoAvaliacaoAnalista where apagado=0 AND codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
                using (SqlCeDataAdapter a = new SqlCeDataAdapter("select cod, nome , 'Formato', 'Cliente' , data_registo from FormatoAvaliacaoCliente where apagado=0 AND codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
                using (SqlCeDataAdapter a = new SqlCeDataAdapter("select cod, nome , 'Formato', 'Profissional' , data_registo from FormatoAvaliacaoProfissional where apagado=0 AND codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
                //avaliacoes
                using (SqlCeDataAdapter a = new SqlCeDataAdapter("select cod, nome , 'Avaliação', 'Analista' , data_registo from AvaliacaoAnalista where codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
                using (SqlCeDataAdapter a = new SqlCeDataAdapter("select cod, nome , 'Avaliação', 'Cliente' , data_registo from AvaliacaoCliente where codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
                using (SqlCeDataAdapter a = new SqlCeDataAdapter("select cod, nome , 'Avaliação', 'Profissional' , data_registo from AvaliacaoProfissional where codAmbC=" + codAmb, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
            }
            return dt;
        }
    }
}

